﻿using CBS.Common;
using CBS.DB.CBSEntity;
using CBS.DB;
using CBS.Model.ManageModel;
using CBS.Model.UIModel;
using CBS.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using MySqlConnector;
using NPOI.HSSF.Record.AutoFilter;
using System.Data.Common;
using CBS.Model.Enum;
using MathNet.Numerics.Distributions;

namespace CBS.APP
{
    public class StudentAmountApp
    {
        public static bool CreateStudentAmount(StudentAmountViewModel amount)
        {
            using CBSContext cbs = new();
            cbs.cbs_student_amount.Add(new cbs_student_amount
            {
                StudentId = amount.StudentId,
                Balance = amount.Balance,
            });

            return cbs.SaveChanges() > 0;
        }

        public static List<StudentAmountViewModel> GetList(Pagination page, string? keyword)
        {
            keyword ??= "";

            List<StudentAmountViewModel> list;
            string sql = $"select a.StudentId,a.StudentName,b.Balance\r\nfrom cbs_student a\r\n\t\tleft join cbs_student_amount b on a.StudentId =  b.StudentId\r\nwhere (@keyword = '' or a.StudentName like @keyword)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@keyword",$"%{keyword}%")
            };

            list = DBHelper.FindListOrderBy<StudentAmountViewModel>(sql, page, FromPositionInSql.First, para);

            return list;
        }

        public static StudentAmountViewModel GetForm(int studentId)
        {
            using CBSContext cbs = new();
            var stu = cbs.cbs_student.FirstOrDefault(t => t.StudentId == studentId);
            var entity = cbs.cbs_student_amount.FirstOrDefault(t => t.StudentId == studentId);

            return new StudentAmountViewModel
            {
                StudentId = studentId,
                StudentName = stu.StudentName,
                Balance = entity.Balance,
            };
        }

        public static StudentSemesterAmountViewModel GetFormById(int studentSemesterAmountId)
        {
            using CBSContext cbs = new();

            var entity = cbs.cbs_student_semester_amount.FirstOrDefault(t => t.SemesterAmountId == studentSemesterAmountId);
            if (entity == null)
            {
                return new StudentSemesterAmountViewModel();
            }
            var stu = cbs.cbs_student.FirstOrDefault(t => t.StudentId == entity.StudentId);
            if (stu == null)
            {
                return new StudentSemesterAmountViewModel();
            }

            var semester = cbs.cbs_semester.FirstOrDefault(t => t.SemesterId == entity.SemesterId);

            return new StudentSemesterAmountViewModel
            {
                SemesterAmountId = entity.SemesterAmountId,
                StudentId = entity.StudentId,
                StudentName = stu.StudentName,
                BeforeSemesterBalance = entity.BeforeSemesterBalance,
                Payable = entity.Payable,
                Payment = entity.Payment,
                SemesterId = entity.SemesterId,
                SemesterName = semester.SemesterName
            };
        }

        public static bool UpdateStudentSemesterAmount(StudentSemesterAmountViewModel model)
        {
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var semesterAmount = cbs.cbs_student_semester_amount.FirstOrDefault(t => t.SemesterAmountId == model.SemesterAmountId);
                if (semesterAmount == null)
                {
                    transaction.Rollback();
                    return false;
                }

                semesterAmount.BeforeSemesterBalance =model.BeforeSemesterBalance;
                semesterAmount.Payable = model.Payable;
                semesterAmount.Payment = model.Payment;

                cbs.cbs_student_semester_amount.Update(semesterAmount);
                cbs.SaveChanges();

                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }


        public static bool StudentAccountRefund(int semesterId, int studentId, decimal amount)
        {
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {

                var entity = cbs.cbs_student_amount.FirstOrDefault(t => t.StudentId == studentId);
                var costInfo = new cbs_student_cost
                {
                    StudentId = studentId,
                    BeforeAmount = entity.Balance,
                    Cost = amount,
                    CostSemesterId = semesterId,
                    CostTime = DateTime.Now,
                    CostType = (int)StudentCostEnum.Refund
                };



                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}
